use crate::models::DataItem;
use r2d2::Pool;
use r2d2_sqlite::SqliteConnectionManager;
use rusqlite::{params, Result};

pub struct DataItemDao {
    pub pool: Pool<SqliteConnectionManager>,
}

impl DataItemDao {
    pub fn new(pool: Pool<SqliteConnectionManager>) -> Self {
        DataItemDao { pool }
    }

    // 创建表
    pub fn create_table(&self) -> Result<()> {
        let conn = self.pool.get().unwrap();
        conn.execute(
            "CREATE TABLE IF NOT EXISTS data_item (
                id INTEGER PRIMARY KEY,
                name TEXT NOT NULL,
                value JSON,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )",
            [],
        )?;
        Ok(())
    }

    // 插入数据
    pub fn insert(&self, data_item: &DataItem) -> Result<(), rusqlite::Error> {
        let mut conn = self.pool.get().unwrap();
        let tx = conn.transaction().unwrap();
        tx.execute(
            "INSERT INTO data_item (name, value) VALUES (?1,?2)",
            params![data_item.name, data_item.value.to_string()],
        )?;
        tx.commit().unwrap();
        Ok(())
    }

    // 查询所有数据
    pub fn get_all_data_items(&self) -> Result<Vec<DataItem>> {
        let conn = self.pool.get().unwrap();
        let mut stmt = conn.prepare("SELECT * FROM data_item").unwrap();
        let rows = stmt.query_map([], |row| {
            Ok(DataItem {
                id: row.get(0)?,
                name: row.get(1)?,
                value: serde_json::from_str(&row.get::<_, String>(2)?).unwrap(),
                created_at: row.get(3)?,
                updated_at: row.get(4)?,
            })
        })?;
        let mut data_items = Vec::new();
        for row in rows {
            data_items.push(row?);
        }
        Ok(data_items)
    }
}
